-- MySQL dump 10.13  Distrib 8.2.0, for Win64 (x86_64)
--
-- Host: localhost    Database: ccms_partb_db
-- ------------------------------------------------------
-- Server version	8.2.0

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `announcement`
--

DROP TABLE IF EXISTS `announcement`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `announcement` (
  `AnnouncementID` int NOT NULL AUTO_INCREMENT,
  `ClubID` int NOT NULL,
  `Title` varchar(150) NOT NULL,
  `MessageBody` text NOT NULL,
  `SentDateTime` datetime NOT NULL,
  `Channel` varchar(30) NOT NULL DEFAULT 'Email',
  PRIMARY KEY (`AnnouncementID`),
  KEY `idx_announcement_club` (`ClubID`),
  CONSTRAINT `announcement_ibfk_1` FOREIGN KEY (`ClubID`) REFERENCES `club` (`ClubID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `announcement_chk_1` CHECK ((`Channel` in (_utf8mb4'Email',_utf8mb4'SMS',_utf8mb4'WhatsApp',_utf8mb4'InApp',_utf8mb4'Other')))
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `announcement`
--

LOCK TABLES `announcement` WRITE;
/*!40000 ALTER TABLE `announcement` DISABLE KEYS */;
INSERT INTO `announcement` VALUES (1,1,'Workshop Reminder','Ethical hacking workshop starts at 10am.','2025-02-05 08:00:00','Email'),(2,2,'Tournament Signup','Register for the chess tournament by Friday.','2025-02-08 12:00:00','InApp'),(3,1,'Meeting Reminder','Reminder: Club meeting holds tomorrow at 2pm.','2025-12-13 02:08:28','Email');
/*!40000 ALTER TABLE `announcement` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `announcementrecipient`
--

DROP TABLE IF EXISTS `announcementrecipient`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `announcementrecipient` (
  `AnnouncementRecipientID` int NOT NULL AUTO_INCREMENT,
  `AnnouncementID` int NOT NULL,
  `StudentID` int NOT NULL,
  `SentDateTime` datetime NOT NULL,
  `DeliveryChannel` varchar(30) NOT NULL DEFAULT 'Email',
  `ReadStatus` varchar(20) NOT NULL DEFAULT 'Unread',
  PRIMARY KEY (`AnnouncementRecipientID`),
  UNIQUE KEY `AnnouncementID` (`AnnouncementID`,`StudentID`),
  KEY `StudentID` (`StudentID`),
  KEY `idx_announcementrecipient_announcement` (`AnnouncementID`),
  CONSTRAINT `announcementrecipient_ibfk_1` FOREIGN KEY (`AnnouncementID`) REFERENCES `announcement` (`AnnouncementID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `announcementrecipient_ibfk_2` FOREIGN KEY (`StudentID`) REFERENCES `student` (`StudentID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `announcementrecipient_chk_1` CHECK ((`ReadStatus` in (_utf8mb4'Unread',_utf8mb4'Read')))
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `announcementrecipient`
--

LOCK TABLES `announcementrecipient` WRITE;
/*!40000 ALTER TABLE `announcementrecipient` DISABLE KEYS */;
INSERT INTO `announcementrecipient` VALUES (1,1,1,'2025-02-05 08:00:10','Email','Read'),(2,1,2,'2025-02-05 08:00:12','Email','Unread'),(3,2,1,'2025-02-08 12:00:05','InApp','Unread'),(4,3,2,'2025-12-13 02:08:28','Email','Unread'),(5,3,3,'2025-12-13 02:08:28','Email','Unread');
/*!40000 ALTER TABLE `announcementrecipient` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `audit_log`
--

DROP TABLE IF EXISTS `audit_log`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `audit_log` (
  `AuditID` int NOT NULL AUTO_INCREMENT,
  `TableName` varchar(50) NOT NULL,
  `OperationType` varchar(10) NOT NULL,
  `RecordID` int NOT NULL,
  `OldData` text,
  `NewData` text,
  `ActionTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`AuditID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `audit_log`
--

LOCK TABLES `audit_log` WRITE;
/*!40000 ALTER TABLE `audit_log` DISABLE KEYS */;
INSERT INTO `audit_log` VALUES (1,'membership','UPDATE',1,'OldStatus=Inactive','NewStatus=Active','2025-12-13 03:25:08'),(2,'membership','INSERT',8,NULL,'StudentID=5, ClubID=3, Status=Active','2025-12-13 03:45:00'),(3,'membership','DELETE',3,'StudentID=2, ClubID=1, Status=Active',NULL,'2025-12-13 03:46:52');
/*!40000 ALTER TABLE `audit_log` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `club`
--

DROP TABLE IF EXISTS `club`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `club` (
  `ClubID` int NOT NULL AUTO_INCREMENT,
  `ClubName` varchar(120) NOT NULL,
  `Category` varchar(30) NOT NULL,
  `FoundedDate` date NOT NULL,
  `Description` varchar(255) DEFAULT NULL,
  `Status` varchar(20) NOT NULL DEFAULT 'Active',
  PRIMARY KEY (`ClubID`),
  UNIQUE KEY `ClubName` (`ClubName`),
  CONSTRAINT `club_chk_1` CHECK ((`Category` in (_utf8mb4'Academic',_utf8mb4'Cultural',_utf8mb4'Sports',_utf8mb4'Special-Interest',_utf8mb4'Volunteer',_utf8mb4'Other'))),
  CONSTRAINT `club_chk_2` CHECK ((`Status` in (_utf8mb4'Active',_utf8mb4'Inactive')))
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `club`
--

LOCK TABLES `club` WRITE;
/*!40000 ALTER TABLE `club` DISABLE KEYS */;
INSERT INTO `club` VALUES (1,'Cybersecurity Society','Academic','2018-09-15','Cybersecurity workshops and CTFs','Active'),(2,'Chess Club','Special-Interest','2016-02-10','Weekly chess games and tournaments','Active'),(3,'Football Club','Sports','2014-05-01','Training and competitive matches','Active');
/*!40000 ALTER TABLE `club` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `event`
--

DROP TABLE IF EXISTS `event`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `event` (
  `EventID` int NOT NULL AUTO_INCREMENT,
  `ClubID` int NOT NULL,
  `EventName` varchar(120) NOT NULL,
  `EventDescription` varchar(500) DEFAULT NULL,
  `Venue` varchar(120) NOT NULL,
  `MaxAttendance` int DEFAULT NULL,
  `IsMembershipOnly` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`EventID`),
  KEY `idx_event_club` (`ClubID`),
  CONSTRAINT `event_ibfk_1` FOREIGN KEY (`ClubID`) REFERENCES `club` (`ClubID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `event_chk_1` CHECK (((`MaxAttendance` is null) or (`MaxAttendance` > 0)))
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `event`
--

LOCK TABLES `event` WRITE;
/*!40000 ALTER TABLE `event` DISABLE KEYS */;
INSERT INTO `event` VALUES (1,1,'Ethical Hacking Workshop','Introduction to ethical hacking tools','Lab A1',50,1),(2,2,'Rapid Chess Tournament','5-minute blitz chess tournament','Hall B',30,0),(3,3,'Football Tryouts','Selection for university football team','Main Field',60,0);
/*!40000 ALTER TABLE `event` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `eventparticipation`
--

DROP TABLE IF EXISTS `eventparticipation`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `eventparticipation` (
  `ParticipationID` int NOT NULL AUTO_INCREMENT,
  `EventID` int NOT NULL,
  `StudentID` int NOT NULL,
  `AttendanceStatus` varchar(20) NOT NULL DEFAULT 'Registered',
  `CheckInTime` datetime DEFAULT NULL,
  PRIMARY KEY (`ParticipationID`),
  UNIQUE KEY `EventID` (`EventID`,`StudentID`),
  KEY `idx_eventparticipation_event` (`EventID`),
  KEY `idx_eventparticipation_student` (`StudentID`),
  CONSTRAINT `eventparticipation_ibfk_1` FOREIGN KEY (`EventID`) REFERENCES `event` (`EventID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `eventparticipation_ibfk_2` FOREIGN KEY (`StudentID`) REFERENCES `student` (`StudentID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `eventparticipation_chk_1` CHECK ((`AttendanceStatus` in (_utf8mb4'Registered',_utf8mb4'Attended',_utf8mb4'NoShow',_utf8mb4'Cancelled')))
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `eventparticipation`
--

LOCK TABLES `eventparticipation` WRITE;
/*!40000 ALTER TABLE `eventparticipation` DISABLE KEYS */;
INSERT INTO `eventparticipation` VALUES (1,1,1,'Attended','2025-02-05 10:01:00'),(2,1,2,'Attended','2025-02-05 10:03:00'),(3,2,1,'NoShow',NULL),(4,3,4,'Attended','2025-02-10 16:10:00');
/*!40000 ALTER TABLE `eventparticipation` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `membership`
--

DROP TABLE IF EXISTS `membership`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `membership` (
  `MembershipID` int NOT NULL AUTO_INCREMENT,
  `StudentID` int NOT NULL,
  `ClubID` int NOT NULL,
  `JoinDate` date NOT NULL,
  `Role` varchar(30) DEFAULT NULL,
  `MembershipStatus` varchar(20) NOT NULL DEFAULT 'Active',
  PRIMARY KEY (`MembershipID`),
  UNIQUE KEY `StudentID` (`StudentID`,`ClubID`),
  KEY `idx_membership_student` (`StudentID`),
  KEY `idx_membership_club` (`ClubID`),
  CONSTRAINT `membership_ibfk_1` FOREIGN KEY (`StudentID`) REFERENCES `student` (`StudentID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `membership_ibfk_2` FOREIGN KEY (`ClubID`) REFERENCES `club` (`ClubID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `membership_chk_1` CHECK ((`MembershipStatus` in (_utf8mb4'Active',_utf8mb4'Inactive')))
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `membership`
--

LOCK TABLES `membership` WRITE;
/*!40000 ALTER TABLE `membership` DISABLE KEYS */;
INSERT INTO `membership` VALUES (1,1,1,'2022-10-02','Member','Active'),(4,3,1,'2023-10-01','Member','Active'),(5,4,3,'2020-10-12','Member','Active'),(7,5,2,'2024-10-01','Member','Active'),(8,5,3,'2024-12-01','Member','Active');
/*!40000 ALTER TABLE `membership` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_membership_after_insert` AFTER INSERT ON `membership` FOR EACH ROW BEGIN
    INSERT INTO audit_log (TableName, OperationType, RecordID, NewData)
    VALUES (
        'membership',
        'INSERT',
        NEW.MembershipID,
        CONCAT(
            'StudentID=', NEW.StudentID,
            ', ClubID=', NEW.ClubID,
            ', Status=', NEW.MembershipStatus
        )
    );
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_membership_after_update` AFTER UPDATE ON `membership` FOR EACH ROW BEGIN
    INSERT INTO audit_log (TableName, OperationType, RecordID, OldData, NewData)
    VALUES (
        'membership',
        'UPDATE',
        OLD.MembershipID,
        CONCAT(
            'OldStatus=', OLD.MembershipStatus
        ),
        CONCAT(
            'NewStatus=', NEW.MembershipStatus
        )
    );
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_membership_after_delete` AFTER DELETE ON `membership` FOR EACH ROW BEGIN
    INSERT INTO audit_log (TableName, OperationType, RecordID, OldData)
    VALUES (
        'membership',
        'DELETE',
        OLD.MembershipID,
        CONCAT(
            'StudentID=', OLD.StudentID,
            ', ClubID=', OLD.ClubID,
            ', Status=', OLD.MembershipStatus
        )
    );
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Table structure for table `student`
--

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `student` (
  `StudentID` int NOT NULL AUTO_INCREMENT,
  `FirstName` varchar(50) NOT NULL,
  `LastName` varchar(50) NOT NULL,
  `Email` varchar(120) NOT NULL,
  `DateOfBirth` date DEFAULT NULL,
  `EnrollmentYear` year DEFAULT NULL,
  `Status` varchar(20) NOT NULL DEFAULT 'Active',
  PRIMARY KEY (`StudentID`),
  UNIQUE KEY `Email` (`Email`),
  CONSTRAINT `student_chk_1` CHECK ((`Status` in (_utf8mb4'Active',_utf8mb4'Inactive')))
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `student`
--

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,'Ama','Mensah','ama.mensah@uni.edu','2003-05-14',2022,'Active'),(2,'Kofi','Boateng','kofi.boateng@uni.edu','2002-11-02',2021,'Active'),(3,'Zara','Ali','zara.ali@uni.edu','2004-03-21',2023,'Active'),(4,'Daniel','Okoro','daniel.okoro@uni.edu','2001-09-08',2020,'Active'),(5,'Fatima','Hassan','fatima.hassan@uni.edu','2003-01-30',2022,'Active');
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2025-12-14 12:45:54
